﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Data;
using LOGISTCBUS.Entities;

namespace LOGISTCBUS.DAO
{
    public class ConfigurationDAO : BaseDAO
    {
        #region public methods
        public Boolean insert(Configuration c)
        {
            var db = new Db();
            db.Connect();
            String sql = "insert into configurations (email, password, host, port) ";
            sql += "values (@email, @password, @host, @port);";
            var parameters = new Dictionary<String, Object>();
            parameters["@email"] = c.Email;
            parameters["@password"] = c.Password;
            parameters["@host"] = c.Host;
            parameters["@port"] = c.Port;
            try
            {
                var sucess = db.Execute(sql, parameters);
                db.Commit();
                db.Disconnect();
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                db.Rollback();
                db.Disconnect();
                return false;
                throw ex;
            }
        }

        public Boolean update(Configuration c)
        {
            var db = new Db();
            db.Connect();
            String sql = "update configurations set email = @email, password = @password, host = @host, port = @port where id= @id;";
            var parameters = new Dictionary<String, Object>();
            parameters["@email"] = c.Email;
            parameters["@password"] = c.Password;
            parameters["@host"] = c.Host;
            parameters["@port"] = c.Port;
            parameters["@id"] = c.Id;
            try
            {
                var sucess = db.Execute(sql, parameters);
                db.Commit();
                db.Disconnect();
                return true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine(ex.Message);
                db.Rollback();
                db.Disconnect();
                return false;
                throw ex;
            }
        }

        public Configuration selectById(Int32 id)
        {
            var db = new Db();
            db.Connect();
            var dt = db.Select("select * from configurations where id = " + id);
            db.Disconnect();
            if (dt == null || dt.Rows.Count <= 0)
                return null;
            DataRow r = dt.Rows[0];
            var obj = new Configuration()
            {
                Id = Convert.ToInt32(r["id"]),
                Email = r["email"].ToString(),
                Password = r["password"].ToString(),
                Host = r["host"].ToString(),
                Port = Convert.ToInt32(r["port"]),
                Removed = Convert.ToBoolean(r["removed"])
            };
            return obj;
        }

        public List<Configuration> select()
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Configuration>();
            var dt = db.Select("select * from configurations where removed = 0 order by id");
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Configuration()
                {
                    Id = Convert.ToInt32(r["id"]),
                    Email = r["email"].ToString(),
                    Password = r["password"].ToString(),
                    Host = r["host"].ToString(),
                    Port = Convert.ToInt32(r["port"]),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst;
        }

        public List<Configuration> select(Int32 page, Int32 pageSize)
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Configuration>();
            var sql = "select * from configurations where removed = 0";
            sql += "order by name";
            var dt = db.Select(sql);
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Configuration()
                {
                    Id = Convert.ToInt32(r["id"]),
                    Email = r["email"].ToString(),
                    Password = r["password"].ToString(),
                    Host = r["host"].ToString(),
                    Port = Convert.ToInt32(r["port"]),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst.Skip((page - 1) * pageSize).Take(pageSize).ToList();
        }

        public List<Configuration> select(String filter)
        {
            var db = new Db();
            db.Connect();
            var lst = new List<Configuration>();
            var dt = db.Select("select * from configurations where removed = 0 and " + getConditions(filter, "email", "password", "host", "port") + " order by id");
            db.Disconnect();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new Configuration()
                {
                    Id = Convert.ToInt32(r["id"]),
                    Email = r["email"].ToString(),
                    Password = r["password"].ToString(),
                    Host = r["host"].ToString(),
                    Port = Convert.ToInt32(r["port"]),
                    Removed = Convert.ToBoolean(r["removed"])
                });
            }
            return lst;
        }

        public Boolean delete(Configuration c)
        {
            var db = new Db();
            db.Connect();
            String sql = getSQLDeleteUpdate("configurations", "id");
            var parameters = new Dictionary<String, Object>();
            parameters["@id"] = c.Id;
            var sucess = db.Execute(sql, parameters);
            db.Commit();
            db.Disconnect();
            return sucess;
        }

        public Boolean delete(List<Configuration> lst)
        {
            var db = new Db();
            db.Connect();
            foreach (var c in lst)
            {
                String sql = getSQLDeleteUpdate("configurations", "id");
                db.Execute(sql);
            }
            db.Commit();
            db.Disconnect();
            return true;
        }
        #endregion
    }
}
